﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MyHelper;

using clf.weighing.entity;

namespace weighing
{
    public class WeighingBillDao : BaseDao<WeighingBill>
    {

        #region instance

        private static WeighingBillDao? instance;
        public static WeighingBillDao Instance => instance ??= new WeighingBillDao();

        public override string GetTableName()
        {
            return TableName.weighing_bill.ToString();
        }
        #endregion

        public int Insert(WeighingBill bill)
        {
            int res;
            string sql = SqlBuilder.GetInsertSql(bill);
            res = Sqlite.Insert(sql);
            if (res <= 0)
            {
                Log.Error($"ERROR result:{res}  weighing:" + sql);
            }
            return res;
        }

        public int Update(WeighingBill bill)
        {
            int res;
            string sql = SqlBuilder.GetUpdateSql(bill);
            res = Sqlite.Update(sql);
            if (res <= 0)
            {
                Log.Error($"ERROR:{res} Update weighing：" + sql);
            }
            return res;
        }

        /// <summary>
        /// 用户是否有 过磅数据
        /// </summary>
        /// <param name="userId"> 用户ID</param>
        /// <returns>True Or False</returns>
        public bool IsUserHasBill(Int64? userId)
        {
            string field = "id";
            String condition = WeighingBillFiled.user_id.ToString() + " = " + userId;
            String sql = SqlBuilder.GetSelectSql(GetTableName(), field, condition, null, null, null, 1, 0);
            var data = Sqlite.Select<WeighingBill>(sql);
            return data.IsNotNullOrEmpty();
        }
        /// <summary>
        /// 判断公司是否有过磅记录
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool IsCompanyHasBill(Int64? id)
        {
            string field = "id";
            String condition = WeighingBillFiled.send_company_id.ToString() + " = " + id
                + " or " +
                 WeighingBillFiled.receive_company_id.ToString() + " = " + id;
            String sql = SqlBuilder.GetSelectSql(GetTableName(), field, condition, null, null, null, 1, 0);
            var data = Sqlite.Select<WeighingBill>(sql);
            return data.IsNotNullOrEmpty();
        }

        /// <summary>
        /// 判断车辆是否有过磅记录
        /// </summary>
        /// <param name="carNumber"></param>
        /// <returns></returns>
        public bool IsCarHasBill(string carNumber)
        {
            string field = "id";
            String condition = $"{WeighingBillFiled.car_number} = '{carNumber}'";
            String sql = SqlBuilder.GetSelectSqlWithSoftDeleteCondition(GetTableName(), field, condition, null, null, null, 1, 0);
            var data = Sqlite.Select<WeighingBill>(sql);
            return data.IsNotNullOrEmpty();
        }
        /// <summary>
        /// 判断物质是否有过磅记录
        /// </summary>
        /// <param name="materilId"></param>
        /// <returns></returns>
        public bool IsMaterialHasBill(Int64? materilId)
        {
            string field = "id";
            String condition = WeighingBillFiled.material_id.ToString() + " = " + $"{materilId}";
            String sql = SqlBuilder.GetSelectSql(GetTableName(), field, condition, null, null, null, 1, 0);
            var data = Sqlite.Select<WeighingBill>(sql);
            return data.IsNotNullOrEmpty();
        }

        public List<WeighingBill> GetNoOutBills(WeightingBillType type)
        {
            String condition = WeighingBillFiled.status.ToString() + $" = 0 and {WeighingBillFiled.type} = {(int)type}";
            String sql = SqlBuilder.GetSelectSqlWithSoftDeleteCondition(GetTableName(), null, condition, null, null, "id asc");
            var data = Sqlite.Select<WeighingBill>(sql);
            return data;
        }

        public List<WeighingBill> GetBills(WeightingBillType type)
        {
            String condition = $"{WeighingBillFiled.type} = {(int)type}";
            String sql = SqlBuilder.GetSelectSqlWithSoftDeleteCondition(GetTableName(), null, condition, null, null, "id desc");
            var data = Sqlite.Select<WeighingBill>(sql);
            return data;
        }

        /// <summary>
        /// 获取当天的数据，或 未完成 的数据
        /// </summary>
        /// <param name="type">出入类型</param>
        /// <returns></returns>
        public List<WeighingBill> GetTodayBills(WeightingBillType type)
        {
            String date = DateTime.Now.ToString("yyyy-MM-dd");
            String condition = $@"{WeighingBillFiled.type} = {(int)type} and {WeighingBillFiled.affiliated_company_id} = {App.CurrentCompanyVo.Id} 
                    and ({WeighingBillFiled.in_time} like '%{date}%' or {WeighingBillFiled.out_time} like '%{date}%' or {WeighingBillFiled.status} ={(int)BillStatus.NoOut} )";
            String sql = SqlBuilder.GetSelectSqlWithSoftDeleteCondition(GetTableName(), null, condition);
            var data = Sqlite.Select<WeighingBill>(sql);
            return data;
        }

        /// <summary>
        /// 获取当天无人值宁的数据
        /// </summary>
        /// <param name="type"> WeightingBillType</param>
        /// <returns></returns>
        public List<WeighingBill> GetTodayUnMannedBills(int DataType)
        {
            String date = DateTime.Now.ToString("yyyy-MM-dd");
            String condition = $@"{WeighingBillFiled.affiliated_company_id} = {App.CurrentCompanyVo.Id} 
                    and ({WeighingBillFiled.in_time} like '%{date}%' or {WeighingBillFiled.out_time} like '%{date}%' or {WeighingBillFiled.status} ={(int)BillStatus.NoOut} )";
            if (DataType == 1)
            {
                condition += $" and {WeighingBillFiled.type} ={(int)WeightingBillType.Buy}";
            }
            else if (DataType == 2)
            {
                condition += $" and {WeighingBillFiled.type} ={(int)WeightingBillType.Sale}";
            }
            else if (DataType == 3)
            {
                condition += $" and {WeighingBillFiled.type} ={(int)WeightingBillType.Self}";
            }
            else if (DataType == 4)
            {
                condition += $" and {WeighingBillFiled.type} ={(int)WeightingBillType.Foreign}";
            }
            else
            {
                //不加条件
            }
            String sql = SqlBuilder.GetSelectSqlWithSoftDeleteCondition(GetTableName(), null, condition);
            var data = Sqlite.Select<WeighingBill>(sql);
            return data;
        }

        public List<WeighingBill> GetTodayUnMannedBills(List<int> types)
        {
            String date = DateTime.Now.ToString("yyyy-MM-dd");
            String condition = $@"{WeighingBillFiled.affiliated_company_id} = {App.CurrentCompanyVo.Id} 
                    and ({WeighingBillFiled.in_time} like '%{date}%' or {WeighingBillFiled.out_time} like '%{date}%' or {WeighingBillFiled.status} ={(int)BillStatus.NoOut} )";
            if (types == null || types.Count == 0)
            {
                return GetTodayUnMannedBills(10);
            }
            else
            {
                string ts = "";
                foreach (int type in types)
                {
                    ts += type + ",";
                }
                string temp = ts.TrimEnd(',');
                condition += $" and ({WeighingBillFiled.type} in ({temp}) or {WeighingBillFiled.status} ={(int)BillStatus.NoOut})";
                String sql = SqlBuilder.GetSelectSqlWithSoftDeleteCondition(GetTableName(), null, condition);
                var data = Sqlite.Select<WeighingBill>(sql);
                return data;
            }
        }

        /// <summary>
        /// 获取报表数据
        /// </summary>
        /// <param name="type"></param>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <param name="sendCompany"></param>
        /// <param name="receiveCompany"></param>
        /// <param name="materailName"></param>
        /// <param name="plate"></param>
        /// <param name="backFieldCondition"> 所有备用字段条件</param>
        /// <returns></returns>
        public List<WeighingBill> GetReportBills(WeightingBillType type, String startDate = null, String endDate = null, string sendCompany = "", string receiveCompany = "", string materailName = "", string plate = "", string backFieldCondition = "")
        {
            String condition = $"{WeighingBillFiled.type} = {(int)type} and {WeighingBillFiled.status} ={(int)BillStatus.Finished}";
            if (startDate.IsNullOrEmpty() && endDate.IsNullOrEmpty())
            {
                var taday = DateTime.Now.ToString("yyyy-MM-dd");
                condition += $" and {WeighingBillFiled.out_time} like '%{taday}%' ";
            }
            else
            {
                if (!startDate.IsNullOrEmpty())
                {
                    condition += $" and {WeighingBillFiled.out_time} >= '{startDate}'";
                }
                if (!endDate.IsNullOrEmpty())
                {
                    condition += $" and {WeighingBillFiled.out_time} <= '{endDate}'";
                }
            }
            if (!sendCompany.IsNullOrEmpty())
            {
                condition += $" and {SendCarBillField.send_company_name} like '%{sendCompany}%'";
            }
            if (!receiveCompany.IsNullOrEmpty())
            {
                condition += $" and {SendCarBillField.receive_company_name} like '%{receiveCompany}%'";
            }

            if (!materailName.IsNullOrEmpty())
            {
                condition += $" and {SendCarBillField.material_name} like '%{materailName}%'";
            }
            if (!plate.IsNullOrEmpty())
            {
                condition += $" and {SendCarBillField.car_number} like '%{plate}%'";
            }
            if (!backFieldCondition.IsNullOrEmpty())
            {
                condition += $" and {backFieldCondition}";
            }
            string order = $"{WeighingBillFiled.id} desc";
            String sql = SqlBuilder.GetSelectSqlWithSoftDeleteCondition(GetTableName(), null, condition, null, null, order, Constant.ReportMaxRows);
            var data = Sqlite.Select<WeighingBill>(sql);
            return data;
        }

        /// <summary>
        /// 获取报表的明细报表的列表统计数据
        /// </summary>
        /// <param name="type">not null </param>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <param name="where">其它条件</param>
        /// <returns></returns>
        public List<ReportWeightListV> GetListStatisticData(WeightingBillType type, String startDate = null, String endDate = null, string sendCompany = "", string receiveCompany = "", string materailName = "", string plate = "", string backFieldCondition ="")
        {
            String infield = $@"{WeighingBillFiled.send_company_name} as company,                                    
                                    {WeighingBillFiled.material_name} as material,
                                    COUNT(DISTINCT({WeighingBillFiled.id})) as cars,
                                    SUM({WeighingBillFiled.gross_weight}) as gross_weight,
                                    SUM({WeighingBillFiled.trae_weight}) as trae_weight,
                                    SUM({WeighingBillFiled.net_weight}) as weight";

            String outfield = $@"{WeighingBillFiled.receive_company_name} as company,                                    
                                    {WeighingBillFiled.material_name} as material,
                                    COUNT(DISTINCT({WeighingBillFiled.id})) as cars,
                                    SUM({WeighingBillFiled.gross_weight}) as gross_weight,
                                    SUM({WeighingBillFiled.trae_weight}) as trae_weight,
                                    SUM({WeighingBillFiled.net_weight}) as weight";

            String inGroupBy = $@" {WeighingBillFiled.send_company_name},{WeighingBillFiled.material_name}";
            String outGroupBy = $@" {WeighingBillFiled.receive_company_name},{WeighingBillFiled.material_name}";
            string condition = $"{WeighingBillFiled.type} = {(int)type} and {WeighingBillFiled.status} ={(int)BillStatus.Finished} and {WeighingBillFiled.is_delete} = 0";
            if (startDate.IsNullOrEmpty() && endDate.IsNullOrEmpty())
            {
                var taday = DateTime.Now.ToString("yyyy-MM-dd");
                condition += $" and {WeighingBillFiled.out_time} like '%{taday}%' ";
            }
            else
            {
                if (!startDate.IsNullOrEmpty())
                {
                    condition += $" and {WeighingBillFiled.out_time} >= '{startDate}'";
                }
                if (!endDate.IsNullOrEmpty())
                {
                    condition += $" and {WeighingBillFiled.out_time} <= '{endDate}'";
                }
            }

            if (!sendCompany.IsNullOrEmpty())
            {
                condition += $" and {SendCarBillField.send_company_name} like '%{sendCompany}%'";
            }
            if (!receiveCompany.IsNullOrEmpty())
            {
                condition += $" and {SendCarBillField.receive_company_name} like '%{receiveCompany}%'";
            }

            if (!materailName.IsNullOrEmpty())
            {
                condition += $" and {SendCarBillField.material_name} like '%{materailName}%'";
            }
            if (!plate.IsNullOrEmpty())
            {
                condition += $" and {SendCarBillField.car_number} like '%{plate}%'";
            }
            if (!backFieldCondition.IsNullOrEmpty())
            {
                condition += $" and {backFieldCondition}";
            }

            string sql;
            if (type == WeightingBillType.Buy)
            {
                sql = SqlBuilder.GetSelectSql(GetTableName(), infield, condition, inGroupBy);
            }
            else if (type == WeightingBillType.Sale)
            {
                sql = SqlBuilder.GetSelectSql(GetTableName(), outfield, condition, outGroupBy);
            }
            else
            {
                sql = SqlBuilder.GetSelectSql(GetTableName(), outfield, condition, outGroupBy);
            }
            var data = Sqlite.Select<ReportWeightListV>(sql);
            return data;
        }

        public List<WeighingBill> SelectTimeRange(string start, String end)
        {
            string condition = $"{WeighingBillFiled.out_time} >= '{start}' and {WeighingBillFiled.out_time} <= '{end}'";
            string sql = SqlBuilder.GetSelectSql(GetTableName(), null, condition);
            var data = Sqlite.Select<WeighingBill>(sql);
            return data;
        }

        /// <summary>
        /// 获取报表的明细报表的 汇总 统计数据
        /// </summary>
        /// <param name="type"></param>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <param name="where">其它条件</param>
        /// <returns></returns>
        public ReportWeightSummaryV GetReportWeightSummaryData(WeightingBillType type, String startDate = null, String endDate = null, string sendCompany = "", string receiveCompany = "", string materailName = "", string plate = "",string backFieldCondition="")
        {
            String infield = $@"COUNT(DISTINCT({WeighingBillFiled.send_company_id})) as companys,
                                COUNT(DISTINCT({WeighingBillFiled.material_id})) as materials,
                                COUNT(DISTINCT({WeighingBillFiled.id})) as cars,
                                SUM({WeighingBillFiled.send_net_weight}) as send_weight,
                                SUM({WeighingBillFiled.gross_weight}) as gross_weight,
                                SUM({WeighingBillFiled.trae_weight}) as trae_weight,
                                SUM({WeighingBillFiled.net_weight}) as net_weight,
                                SUM({WeighingBillFiled.decuation_weight}) as decuation_weight,
                                SUM({WeighingBillFiled.diff_weight}) as diff_weight";

            String outfield = $@"COUNT(DISTINCT({WeighingBillFiled.receive_company_id})) as companys,
                                 COUNT(DISTINCT({WeighingBillFiled.material_id})) as materials,
                                 COUNT(DISTINCT({WeighingBillFiled.id})) as cars,
                                 SUM({WeighingBillFiled.gross_weight}) as gross_weight,
                                 SUM({WeighingBillFiled.trae_weight}) as trae_weight,
                                 SUM({WeighingBillFiled.net_weight}) as net_weight";


            String mixturefield = $@"COUNT(DISTINCT({WeighingBillFiled.material_id})) as materials,
                                     COUNT(DISTINCT({WeighingBillFiled.id})) as cars,
                                     SUM({WeighingBillFiled.gross_weight}) as gross_weight,
                                     SUM({WeighingBillFiled.trae_weight}) as trae_weight,
                                     SUM({WeighingBillFiled.net_weight}) as net_weight";

            string condition = $"{WeighingBillFiled.type} = {(int)type} and {WeighingBillFiled.status} = {(int)BillStatus.Finished} and {WeighingBillFiled.is_delete} = 0";

            if (startDate.IsNullOrEmpty() && endDate.IsNullOrEmpty())
            {
                var taday = DateTime.Now.ToString("yyyy-MM-dd");
                condition += $" and {WeighingBillFiled.out_time} like '%{taday}%' ";
            }
            else
            {
                if (!startDate.IsNullOrEmpty())
                {
                    condition += $" and {WeighingBillFiled.out_time} >= '{startDate}'";
                }
                if (!endDate.IsNullOrEmpty())
                {
                    condition += $" and {WeighingBillFiled.out_time} <= '{endDate}'";
                }
            }

            if (!sendCompany.IsNullOrEmpty())
            {
                condition += $" and {SendCarBillField.send_company_name} like '%{sendCompany}%'";
            }
            if (!receiveCompany.IsNullOrEmpty())
            {
                condition += $" and {SendCarBillField.receive_company_name} like '%{receiveCompany}%'";
            }

            if (!materailName.IsNullOrEmpty())
            {
                condition += $" and {SendCarBillField.material_name} like '%{materailName}%'";
            }
            if (!plate.IsNullOrEmpty())
            {
                condition += $" and {SendCarBillField.car_number} like '%{plate}%'";
            }
            if (!backFieldCondition.IsNullOrEmpty())
            {
                condition += $" and {backFieldCondition}";
            }

            string sql;
            if (type == WeightingBillType.Buy)
            {
                sql = SqlBuilder.GetSelectSql(GetTableName(), infield, condition);
            }
            else if (type == WeightingBillType.Sale)
            {
                sql = SqlBuilder.GetSelectSql(GetTableName(), outfield, condition);
            }
            else if (type == WeightingBillType.Mixture)
            {
                sql = SqlBuilder.GetSelectSql(GetTableName(), mixturefield, condition);
            }
            else
            {
                sql = SqlBuilder.GetSelectSql(GetTableName(), outfield, condition);
            }
            var data = Sqlite.Select<ReportWeightSummaryV>(sql);
            
            return data.FirstOrDefault();
        }

        public bool SoftDelete(string number, string reason = null)
        {
            if (string.IsNullOrEmpty(number)) return false;
            string set = $"{WeighingBillFiled.is_delete} = 1,{WeighingBillFiled.delete_time}='{DateTimeHelper.getCurrentDateTime()}'";
            if (!string.IsNullOrEmpty(reason))
            {
                set += $",{WeighingBillFiled.delete_reason} = '{reason}'";
            }
            set += $",{WeighingBillFiled.up_government_status} = 0";//重置上传次数，上传删除服务器数据
            string condition = $"{WeighingBillFiled.number} = '{number}'";
            string sql = SqlBuilder.GetUpdateSql(GetTableName(), set, condition);
            int res = Sqlite.Update(sql);
            return res > 0;
        }

        /// <summary>
        /// 获取需要上传的磅单 默认10条 ,上传次数小于2 的 ,测试的数据不上传
        /// </summary>
        /// <param name="rows">默认10条</param>
        /// <returns></returns>
        public List<WeighingBill> GetGovernmentNoUps(int rows = 10)
        {
            String condition = $"{WeighingBillFiled.up_government_status} <{2} and {WeighingBillFiled.data_type} = {(int)DataType.Prod}";
            String order = $"{WeighingBillFiled.status} desc,{WeighingBillFiled.id} asc";
            string sql = SqlBuilder.GetSelectSql(GetTableName(), null, condition, null, null, order, rows);
            var data = Sqlite.Select<WeighingBill>(sql);
            return data;
        }

        /// <summary>
        /// 获取需要上传的磅单 默认10条 ,上传次数小于2 的 ,测试的数据不上传
        /// </summary>
        /// <param name="rows">默认10条</param>
        /// <returns></returns>
        public List<WeighingBill> GetOfficalNoUps(int rows = 10)
        {
            String condition = $"{WeighingBillFiled.up_offical_status} <{2} and {WeighingBillFiled.data_type} = {(int)DataType.Prod}";
            String order = $"{WeighingBillFiled.status} desc,{WeighingBillFiled.id} asc";
            string sql = SqlBuilder.GetSelectSql(GetTableName(), null, condition, null, null, order, rows);
            var data = Sqlite.Select<WeighingBill>(sql);
            return data;
        }

        /// <summary>
        /// 获取需要上传的磅单 总条数
        /// </summary>
        /// <param name="rows">默认10条</param>
        /// <returns></returns>
        public int GetGovernmentNoUps()
        {
            String condition = $"{WeighingBillFiled.up_government_status} <{2} and {WeighingBillFiled.data_type} = {(int)DataType.Prod}";
            string sql = SqlBuilder.GetSelectSqlWithSoftDeleteCondition(GetTableName(), " id", condition);
            var data = Sqlite.Select<WeighingBill>(sql);
            return data.Count;
        }
        /// <summary>
        /// 获取需要上传的磅单 总条数
        /// </summary>
        /// <param name="rows">默认10条</param>
        /// <returns></returns>
        public int GetOfficalNoUps()
        {
            String condition = $"{WeighingBillFiled.up_offical_status} <{2} and {WeighingBillFiled.data_type} = {(int)DataType.Prod}";
            string sql = SqlBuilder.GetSelectSqlWithSoftDeleteCondition(GetTableName(), " id", condition);
            var data = Sqlite.Select<WeighingBill>(sql);
            return data.Count;
        }

        public int ChangeStatus(string number, BillStatus status)
        {
            if (string.IsNullOrEmpty(number))
            {
                return 0;
            }
            string condition = $"{WeighingBillFiled.number} = '{number}'";
            string set = $"{WeighingBillFiled.status} = {(int)status}";
            set += $",{WeighingBillFiled.up_offical_status} = 1";//重置上传次数，上传修改服务器数据
            string sql = SqlBuilder.GetUpdateSql(GetTableName(), set, condition);
            int res = Sqlite.Update(sql);
            return res;
        }

        public int AfterUpLoadUpdate(long id, int upstatus)
        {
            if (id <= 0) return 0;
            String condition = $"{WeighingBillFiled.id} ={id}";
            string set = $"{WeighingBillFiled.up_government_status} = {upstatus}";
            string sql = SqlBuilder.GetUpdateSql(GetTableName(), set, condition);
            return Sqlite.Update(sql);
        }
        /// <summary>
        /// 是否还有未完成的过磅 区分类型
        /// </summary>
        /// <param name="type"></param>
        /// <param name="carNumber"></param>
        /// <returns></returns>
        public bool IsCarHasNofinishBill(WeightingBillType type, string carNumber)
        {
            String condition = $"{WeighingBillFiled.type} ={(int)type} and status = 0 and {WeighingBillFiled.car_number} = '{carNumber}'";
            string filed = "id";
            string sql = SqlBuilder.GetSelectSqlWithSoftDeleteCondition(GetTableName(), filed, condition, null, null, null, 1);
            var data = Sqlite.Select<WeighingBill>(sql);
            return data.Count > 0;
        }
        /// <summary>
        /// 是否还有未完成的过磅
        /// </summary>
        /// <param name="carNumber"></param>
        /// <returns></returns>
        public bool IsCarHasNofinishBill(string carNumber)
        {
            String condition = $"status = 0 and {WeighingBillFiled.car_number} = '{carNumber}'";
            string filed = "id";
            string sql = SqlBuilder.GetSelectSqlWithSoftDeleteCondition(GetTableName(), filed, condition, null, null, null, 1);
            var data = Sqlite.Select<WeighingBill>(sql);
            return data.Count > 0;
        }
        /// <summary>
        /// 磅单是否未完成，
        /// </summary>
        /// <param name="number">磅单编号</param>
        /// <returns></returns>
        public bool IsHasNofinishBill(string number)
        {
            var bill = this.FindByNumber(number);
            return bill?.status == (int)BillStatus.NoOut;
        }

        public WeighingBill? FindByNumber(string number)
        {
            String condition = $"{WeighingBillFiled.number} ='{number}'";
            string sql = SqlBuilder.GetSelectSqlWithSoftDeleteCondition(GetTableName(), null, condition, null, null, null, 1);
            var list = Sqlite.Select<WeighingBill>(sql);
            return list.FirstOrDefault();
        }
        /// <summary>
        /// 获取已过磅数
        /// </summary>
        /// <returns></returns>
        public int Count()
        {
            string condition = $"{WeighingBillFiled.affiliated_company_id} = {App.CurrentCompanyVo.Id} ";
            string sql = SqlBuilder.GetSelectSqlWithSoftDeleteCondition(GetTableName(), $"{WeighingBillFiled.id}", condition);
            var list = Sqlite.Select<WeighingBill>(sql);
            return list.Count;
        }

        public bool HasDatas()
        {
            string condition = $"{WeighingBillFiled.affiliated_company_id} = {App.CurrentCompanyVo.Id} ";
            string sql = SqlBuilder.GetSelectSqlWithSoftDeleteCondition(GetTableName(), $"{WeighingBillFiled.id}", condition);
            var list = Sqlite.Select<WeighingBill>(sql);
            return (list?.Count > 0);
        }

        public int SoftDeleteAll()
        {
            string reason = "切换单位";
            string set = $"{WeighingBillFiled.is_delete} = 1,{WeighingBillFiled.delete_time}='{DateTimeHelper.getCurrentDateTime()}'";
            if (!string.IsNullOrEmpty(reason))
            {
                set += $",{WeighingBillFiled.delete_reason} = '{reason}'";
            }
            set += $",{WeighingBillFiled.up_government_status} = 0";//重置上传次数，上传删除服务器数据
            string condition = $"{WeighingBillFiled.affiliated_company_id} = {App.CurrentCompanyVo.Id} ";
            string sql = SqlBuilder.GetUpdateSql(GetTableName(), set, condition);
            int res = Sqlite.Update(sql);
            return res;
        }

        public WeighingBill FindNoFinishedByCar(string plate)
        {
            string condition = $"{WeighingBillFiled.affiliated_company_id} = {App.CurrentCompanyVo.Id} " +
                $"and {WeighingBillFiled.car_number} = '{plate}' " +
                $"and {WeighingBillFiled.status} = 0";
            string order = $"{WeighingBillFiled.add_time} desc";
            string sql = SqlBuilder.GetSelectSqlWithSoftDeleteCondition(GetTableName(), null, condition, null, null, order, 1);
            var data = Sqlite.Select<WeighingBill>(sql);
            return data.Count > 0 ? data[0] : null;
        }

        public WeighingBill FindBySendBillID(long? sendBillId)
        {
            string condition = $"{WeighingBillFiled.affiliated_company_id} = {App.CurrentCompanyVo.Id} " +
              $"and {WeighingBillFiled.send_car_bill_id} = {sendBillId} " +
              $"and {WeighingBillFiled.status} = 0";
            string order = $"{WeighingBillFiled.add_time} desc";
            string sql = SqlBuilder.GetSelectSqlWithSoftDeleteCondition(GetTableName(), null, condition, null, null, order, 1);
            var data = Sqlite.Select<WeighingBill>(sql);
            return data.Count > 0 ? data[0] : null;
        }

        /// <summary>
        /// 获取当天过磅数
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        public int GetTodayCount()
        {
            String condition = WeighingBillFiled.add_time + " like '%" + DateTimeHelper.getCurrentDateTime(DateTimeHelper.DateFormat) + "%'";
            string sql = SqlBuilder.GetSelectSql(GetTableName(), WeighingBillFiled.id.ToString(), condition);
            List<WeighingBill> list = Sqlite.Select<WeighingBill>(sql);
            return list.Count;
        }

        /// <summary>
        /// 获取当天当前的出、入库过磅数
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        public int GetTodayCount(WeightingBillType type)
        {
            String condition = WeighingBillFiled.type.ToString() + "=" + ((int)type);
            condition += " and " + WeighingBillFiled.add_time + " like '%" + DateTimeHelper.getCurrentDateTime(DateTimeHelper.DateFormat) + "%'";
            string sql = SqlBuilder.GetSelectSql(GetTableName(), WeighingBillFiled.id.ToString(), condition);
            List<WeighingBill> list = Sqlite.Select<WeighingBill>(sql);
            return list.Count;
        }

        public int ClearByType(WeightingBillType billType)
        {
            string sql = $"DELETE FROM {GetTableName()} where {WeighingBillFiled.type} =  {(int)billType} ;";
            int res = Sqlite.ExecuteNonQuery(sql);
            return res;
        }

        public List<WeighingBillSummaryReportVo> SelectSummaryReports(
            WeightingBillType billType, int year, int month, int day,
            long sendCompanyId,
            long receiveCompanyId,
            long materialId,
            string carNumber,
            bool isGroupSend,
            bool isGroupReceive,
            bool isGroupMaterial,
            bool isGroupCar,
            bool isGroupMonth,
            bool isGroupDay)
        {
            string fields = "";
            string condition = "";
            string order = $"{WeighingBillFiled.in_time} asc";
            string group = "";
            string format = "%Y";
            int len = 5;
            string timeLike = $"{year}%";
            string sm = month.ToString();
            if (sm.Length == 1)
            {
                sm = "0" + sm;
            }
            string ds = day.ToString();
            if (ds.Length == 1) ds = "0" + ds;

            if (isGroupMonth || isGroupDay)
            {
                if (isGroupMonth)
                {
                    format += "-%m";
                    len = 8;
                }
                else if (isGroupDay)
                {
                    format += "-%m-%d";
                    // 按天，传入月份
                    timeLike = $"{year}-{sm}%";
                    len = 11;
                }

            }
            else
            {
                if (month > 0)
                {
                    format += "-%m";
                    len = 8;
                    timeLike = $"{year}-{sm}%";
                }

                if (day > 0)
                {
                    format += "-%d";
                    len = 11;
                    timeLike = $"{year}-{sm}-{ds}%";
                }
            }

            group = "time";
            fields = $"Count(DISTINCT id) as cars ,substr(`{WeighingBillFiled.out_time}`,0,'{len}') as time,sum(`{WeighingBillFiled.net_weight}`) as net_weight";
            condition = $"{WeighingBillFiled.status} = 1 and {WeighingBillFiled.is_delete} = 0 and {WeighingBillFiled.out_time} like '{timeLike}'";
            condition += $" and {WeighingBillFiled.type} = {(int)billType} ";
            if (sendCompanyId > 0)
            {
                condition += $" and {WeighingBillFiled.send_company_id} = {sendCompanyId}";
            }

            if (receiveCompanyId > 0)
            {
                condition += $" and {WeighingBillFiled.receive_company_id} = {receiveCompanyId}";
            }

            if (materialId > 0)
            {
                condition += $" and {WeighingBillFiled.material_id} = {materialId}";
            }

            if (!carNumber.IsNullOrEmpty())
            {
                condition += $" and {WeighingBillFiled.car_number} = '{carNumber}'";
            }

            if (isGroupMonth || isGroupDay)
            {
                //都 不加          

            }
            else
            {
                if (isGroupSend)
                {
                    fields += $",{WeighingBillFiled.send_company_name}";
                    group += $",{WeighingBillFiled.send_company_name}";
                }

                if (isGroupReceive)
                {
                    fields += $",{WeighingBillFiled.receive_company_name}";
                    group += $",{WeighingBillFiled.receive_company_name}";
                }

                if (isGroupMaterial)
                {
                    fields += $",{WeighingBillFiled.material_name}";
                    group += $",{WeighingBillFiled.material_name}";
                }
                if (isGroupCar)
                {
                    fields += $",{WeighingBillFiled.car_number}";
                    group += $",{WeighingBillFiled.car_number}";
                }
            }

            if (isGroupMonth || isGroupDay)
            {
                //都 不加          

            }
            else
            {
                //主动加上公司名称和分组
                if (day <= 0)
                {
                    if (billType == WeightingBillType.Buy)
                    {
                        if (!fields.Contains(WeighingBillFiled.send_company_name.ToString()))
                        {
                            fields += $",{WeighingBillFiled.send_company_name}";
                        }
                        if (!group.Contains(WeighingBillFiled.send_company_name.ToString()))
                        {
                            group += $",{WeighingBillFiled.send_company_name}";
                        }
                    }
                    else
                    {
                        if (!fields.Contains(WeighingBillFiled.receive_company_name.ToString()))
                        {
                            fields += $",{WeighingBillFiled.receive_company_name}";
                        }
                        if (!group.Contains(WeighingBillFiled.receive_company_name.ToString()))
                        {
                            group += $",{WeighingBillFiled.receive_company_name}";
                        }
                    }
                }
            }

            string sql = SqlBuilder.GetSelectSql(GetTableName(), fields, condition, group, null, order);

            var list = Sqlite.Select<WeighingBillSummaryReportVo>(sql);

            return list;
        }


        public List<WeighingBillSummaryReportVo> SelectSummaryReportsInDate(
         WeightingBillType billType, string startDate,string endDate,
         long sendCompanyId,
         long receiveCompanyId,
         long materialId,
         string carNumber,
         bool isGroupSend,
         bool isGroupReceive,
         bool isGroupMaterial,
         bool isGroupCar)
        {
            string fields = "";
            string condition = "";
            string order = $"{WeighingBillFiled.in_time} asc";
            string group = "";
            string format = "%Y-%m-%d";
            int len = 11;
            group = "time";
            fields = $"Count(DISTINCT id) as cars ,substr(`{WeighingBillFiled.out_time} `,0,' {len}') as time,sum(`{WeighingBillFiled.net_weight}`) as net_weight";
            condition = $"{WeighingBillFiled.status} = 1 and {WeighingBillFiled.is_delete} = 0 ";
            condition += $"and {WeighingBillFiled.out_time} >= '{startDate}' and {WeighingBillFiled.out_time} <= '{endDate}'";
            condition += $" and {WeighingBillFiled.type} = {(int)billType} ";
            if (sendCompanyId > 0)
            {
                condition += $" and {WeighingBillFiled.send_company_id} = {sendCompanyId}";
            }

            if (receiveCompanyId > 0)
            {
                condition += $" and {WeighingBillFiled.receive_company_id} = {receiveCompanyId}";
            }

            if (materialId > 0)
            {
                condition += $" and {WeighingBillFiled.material_id} = {materialId}";
            }

            if (!carNumber.IsNullOrEmpty())
            {
                condition += $" and {WeighingBillFiled.car_number} = '{carNumber}'";
            }

            if (isGroupSend)
            {
                fields += $",{WeighingBillFiled.send_company_name}";
                group += $",{WeighingBillFiled.send_company_name}";
            }

            if (isGroupReceive)
            {
                fields += $",{WeighingBillFiled.receive_company_name}";
                group += $",{WeighingBillFiled.receive_company_name}";
            }

            if (isGroupMaterial)
            {
                fields += $",{WeighingBillFiled.material_name}";
                group += $",{WeighingBillFiled.material_name}";
            }
            if (isGroupCar)
            {
                fields += $",{WeighingBillFiled.car_number}";
                group += $",{WeighingBillFiled.car_number}";
            }

            string sql = SqlBuilder.GetSelectSql(GetTableName(), fields, condition, group, null, order);

            var list = Sqlite.Select<WeighingBillSummaryReportVo>(sql);

            return list;
        }

        public WeighingBill FindLastByPlate(string currplate)
        {
            String condition = $" {WeighingBillFiled.car_number} = '{currplate}'";
            string order = $" {WeighingBillFiled.add_time} desc ";
            string sql = SqlBuilder.GetSelectSqlWithSoftDeleteCondition(GetTableName(), null, condition, null, null, order, 1);
            List<WeighingBill> list = Sqlite.Select<WeighingBill>(sql);
            return list.Count > 0 ? list[0] : null;
        }
        /// <summary>
        /// TODO  获取剩余 
        /// </summary>
        /// <param name="mWeighingBill">磅单</param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public decimal GetSurplusTone(WeighingBill mWeighingBill)
        {
            return 0;
        }
        //根据时间 获取没有打印的磅单
        public WeighingBill FindFirstNoPrintByTime(DateTime lastPrintBilloutTime)
        {
            string time = lastPrintBilloutTime.ToString("yyyy-MM-dd HH:mm:ss");
            String condition = $" {WeighingBillFiled.out_time} >= '{time}' and {WeighingBillFiled.print_times} < 1 and {WeighingBillFiled.status} = {(int)BillStatus.Finished}";
            string order = $" {WeighingBillFiled.add_time} ";
            string sql = SqlBuilder.GetSelectSqlWithSoftDeleteCondition(GetTableName(), null, condition, null, null, order, 1);
            List<WeighingBill> list = Sqlite.Select<WeighingBill>(sql);
            return list.Count > 0 ? list[0] : null;
        }

        public WeighingBill FindLastByType(WeightingBillType type, bool exceludeDelete = false)
        {
            string time = DateTime.Now.ToString("yyyy-MM-dd");
            String condition = $" {WeighingBillFiled.type} = {(int)type} and {WeighingBillFiled.add_time} like '{time}%'";
            string order = $" {WeighingBillFiled.add_time} desc ";
            string sql = exceludeDelete ? SqlBuilder.GetSelectSqlWithSoftDeleteCondition(GetTableName(), null, condition, null, null, order, 1)
                : SqlBuilder.GetSelectSql(GetTableName(), null, condition, null, null, order, 1);
            List<WeighingBill> list = Sqlite.Select<WeighingBill>(sql);
            return list.Count > 0 ? list[0] : null;
        }


        public WeighingBill FindFinishedByPlateAndTime(string carNumber, DateTime addTime)
        {
            string condition = $"{WeighingBillFiled.status} = 1 and {WeighingBillFiled.car_number} = '{carNumber}' and {WeighingBillFiled.out_time} >= '{addTime}'";
            string sql = SqlBuilder.GetSelectSqlWithSoftDeleteCondition(GetTableName(), null, condition, 1);
            var list = Sqlite.Select<WeighingBill>(sql);
            if (list != null && list.Count > 0)
            {
                return list[0];
            }
            else
            {
                return null;
            }
        }

        public int CountNoFinishByMaterial(long materialId)
        {
            string condition = $"{WeighingBillFiled.status} = {(int)BillStatus.NoOut} and {WeighingBillFiled.material_id} = '{materialId}' and {WeighingBillFiled.is_delete} = '0'";
            string sql = SqlBuilder.GetSelectSql(GetTableName(), null, condition);
            var list = Sqlite.Select<WeighingBill>(sql);
            return list?.Count() ?? 0;
        }

        public WeighingBill? FindLastByPlateAndTime(string carNumber, DateTime addTime)
        {
            string intime = addTime.ToString(Constant.DateTimeFormat) ?? DateTime.Now.ToString(Constant.DateTimeFormat);
            string condition = $"{WeighingBillFiled.car_number} = '{carNumber}' and {WeighingBillFiled.in_time} >= '{intime}'";
            string orderBy = $"{WeighingBillFiled.add_time} desc";
            string sql = SqlBuilder.GetSelectSqlWithSoftDeleteCondition(GetTableName(), null, condition, null, null, orderBy, 1);
            var list = Sqlite.Select<WeighingBill>(sql);
            return list?.FirstOrDefault();
        }

        public int SoftDeleteBytype(WeightingBillType billType)
        {
            string time = DateTime.Now.ToString(Constant.DateTimeFormat);
            string condition = $"{WeighingBillFiled.type} = {(int)billType} and {WeighingBillFiled.is_delete} = 0";
            string set = $"{WeighingBillFiled.is_delete} = 1 ,{WeighingBillFiled.delete_reason} = '清空数据',{WeighingBillFiled.delete_time}='{time}'";
            string sql = SqlBuilder.GetUpdateSql(GetTableName(), set, condition);
            int res = Sqlite.ExecuteNonQuery(sql);
            return res;
        }

        /// <summary>
        ///获取最后未完成的数据.
        //1.如果当前是过毛，就获取最后一有皮重，未完成的数据 
        //2.如果当前是过皮，就获取最后一有毛重，未完成的数据 
        /// </summary>
        /// <param name="carNumber"> 车牌</param>
        /// <param name="isGross">是否为过毛重</param>
        /// <returns></returns>
        public WeighingBill? FindLastNoFinishByWeight(string carNumber, bool isGross)
        {
            string condition = $"{WeighingBillFiled.type} = {(int)WeightingBillType.Mixture} and {WeighingBillFiled.status} = {(int)BillStatus.NoOut}";
            if (isGross)
            {
                condition += $" and {WeighingBillFiled.trae_weight} > 0 ";
            }
            else
            {
                condition += $" and {WeighingBillFiled.gross_weight} > 0 ";
            }
            condition += $" and {WeighingBillAuditField.car_number} like '%{carNumber}%'";
            string order = $"{WeighingBillAuditField.add_time} desc";
            string sql = SqlBuilder.GetSelectSqlWithSoftDeleteCondition(GetTableName(), null, condition,null,null,order,1);
            var res = Sqlite.Select<WeighingBill>(sql);
            return res.FirstOrDefault();
        }

        /// <summary>
        ///获取车牌最后未完成的数据.
        /// </summary>
        /// <param name="carNumber"> 车牌</param>
        /// <returns></returns>
        public WeighingBill? FindLastNoFinish(string carNumber)
        {
            string condition = $"{WeighingBillFiled.type} = {(int)WeightingBillType.Mixture} and {WeighingBillFiled.status} = {(int)BillStatus.NoOut}";          
            condition += $" and {WeighingBillAuditField.car_number} like '%{carNumber}%'";
            string order = $"{WeighingBillAuditField.add_time} desc";
            string sql = SqlBuilder.GetSelectSqlWithSoftDeleteCondition(GetTableName(), null, condition, null, null, order, 1);
            var res = Sqlite.Select<WeighingBill>(sql);
            return res.FirstOrDefault();
        }

        internal List<WeighingBill> Select(string sql)
        {
            return Sqlite.Select<WeighingBill>(sql);
        }

        internal bool Exist(string number)
        {
            throw new NotImplementedException();
        }
    }
}
